In MySQL, the functions `COUNT`, `MAX`, `MIN`, `SUM`, and `AVG` are aggregate functions that provide a way to calculate summary values for a set of rows. Let’s delve into each of these functions and their differences, and how you might use them with PHP.
`COUNT` is used to count the number of rows in a result set, or the number of non-NULL values in a column. It is often used to determine the size of a dataset or to count the occurrences of specific criteria.
Example in MySQL:
```
SELECT COUNT AS total_rows FROM employees;
```
This query will return the total number of rows in the `employees` table.
PHP Example:
```
$sql = “SELECT COUNT AS total_rows FROM employees”;
$result = $conn->query($sql);
$row = $result->fetch_assoc();
echo “Total Rows: “ . $row[‘total_rows’];
```
`MAX` is used to find the highest value in a specified column. It ignores NULL values.
Example in MySQL:
```
SELECT MAX AS highest_salary FROM employees;
```
This query will return the highest salary in the `employees` table.
PHP Example:
```
$sql = “SELECT MAX AS highest_salary FROM employees”;
$result = $conn->query($sql);
$row = $result->fetch_assoc();
echo “Highest Salary: “ . $row[‘highest_salary’];
```
`MIN` is used to find the lowest value in a specified column. It also ignores NULL values.
Example in MySQL:
```
SELECT MIN AS lowest_salary FROM employees;
```
This query will return the lowest salary in the `employees` table.
PHP Example:
```
$sql = “SELECT MIN AS lowest_salary FROM employees”;
$result = $conn->query($sql);
$row = $result->fetch_assoc();
echo “Lowest Salary: “ . $row[‘lowest_salary’];
```
`SUM` is used to calculate the total sum of a numeric column. It will add up all the values in the specified column and return the total.
Example in MySQL:
```
SELECT SUM AS total_salary FROM employees;
```
This query will return the total sum of all salaries in the `employees` table.
PHP Example:
```
$sql = “SELECT SUM AS total_salary FROM employees”;
$result = $conn->query($sql);
$row = $result->fetch_assoc();
echo “Total Salary: “ . $row[‘total_salary’];
```
`AVG` is used to calculate the average of a numeric column. It adds up all the values and divides by the number of non-NULL values.
Example in MySQL:
```
SELECT AVG AS average_salary FROM employees;
```
This query will return the average salary in the `employees` table.
PHP Example:
```
$sql = “SELECT AVG AS average_salary FROM employees”;
$result = $conn->query($sql);
$row = $result->fetch_assoc();
echo “Average Salary: “ . $row[‘average_salary’];
```
- [MySQL Documentation](https://dev.mysql.com/doc/)
- [PHP Manual](https://www.php.net/manual/)
- [W3Schools SQL Tutorial](https://www.w3schools.com/sql/sql_func_avg.asp)
In summary, `COUNT` helps you count rows or non-NULL values, `MAX` and `MIN` find the highest and lowest values respectively, `SUM` adds up all numeric values in a column, and `AVG` calculates the average value by dividing the total sum by the number of values. These aggregate functions are fundamental for data analysis and reporting in MySQL, and their application with PHP using MySQLi or PDO can make your web applications highly data-driven and informative.
